#include <soar.h>

#include "common.h"

int insert()
{/*{{{*/
    char buf[BUFFER_SIZE];
    int res;

    printf("insert\n");

    sprintf(buf,
            "insert into my_emp "
            "values(100, sysdate(), '职员100', '8', 34, '云南')");
    printf("SQL: %s\n", buf);
    res=mysqlc_exec(buf);
    if(res != MY_OK)
    {
        printf("错误，sqlcode:%d\n", res);
        return(-1);
    }

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int delete()
{/*{{{*/
    char buf[BUFFER_SIZE];
    int res;

    printf("delete\n");

    sprintf(buf,
            "delete from my_emp where empno=100");
    res=mysqlc_exec(buf);
    if(res != MY_OK)
    {
        printf("错误，sqlcode:%d\n", res);
        return(-1);
    }

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int update()
{/*{{{*/
    char buf[BUFFER_SIZE];
    int res;

    printf("update\n");

    sprintf(buf,
            "update my_emp set upd_ts=sysdate() where empno=%d",
            random_int(1, 10));
    res=mysqlc_exec(buf);
    if(res != MY_OK && res != MY_NO_DATA_FOUND)
    {
        printf("错误，sqlcode:%d\n", res); 
        return(-1);
    }
    if(res == MY_NO_DATA_FOUND)
    {
        printf("未找到记录\n"); 
    }

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int query1()
{/*{{{*/
    struct dbc_rset rset;
    int request_row_num;
    struct my_emp my_emp;
    int row_idx;
    char buf[BUFFER_SIZE];
    int res;

    printf("query1\n");

    sprintf(buf, "select * from my_emp where age>10 order by empno asc");
    printf("SQL: %s\n", buf);
    request_row_num=8;
    res=mysqlc_query(buf, request_row_num, &rset);
    if(res != MY_OK)
    {
        printf("错误，sqlcode:%d\n", res);
        return(-1);
    }
    printf("结果：\n");
    for(row_idx=0; row_idx<rset.row_num; row_idx++)
    {
        my_emp.empno=dbc_col_export_int64(&rset, row_idx, 0);
        dbc_col_export_char(&rset, row_idx, 1, my_emp.upd_ts);
        dbc_col_export_char(&rset, row_idx, 2, my_emp.name);
        dbc_col_export_char(&rset, row_idx, 3, my_emp.duty);
        my_emp.age=dbc_col_export_int(&rset, row_idx, 4);
        printf("%ld|%s|%s|%s|%d\n", my_emp.empno, my_emp.upd_ts,
            my_emp.name, my_emp.duty, my_emp.age);
    }
    if(rset.query_flag == QUERY_BOTTOM)
        printf("查询到底\n");
    else
        printf("查询中途\n");
    dbc_query_free(&rset);

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int query2()
{/*{{{*/
    struct dbc_rset rset;
    char buf[BUFFER_SIZE];
    int res;
    int count, sum;

    printf("query2\n");

    sprintf(buf, "select count(*),sum(age) from my_emp where age>20");
    printf("SQL: %s\n", buf);
    res=mysqlc_query(buf, -1, &rset);
    if(res != MY_OK)
    {
        printf("错误, sqlcode:%d\n", res);
        return(-1);
    }
    count=dbc_col_export_int(&rset, 0, 0);
    sum=dbc_col_export_int(&rset, 0, 1);
    printf("结果：%d %d\n", count, sum);
    dbc_query_free(&rset);

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int query3()
{/*{{{*/
    struct dbc_rset rset;
    int row_idx;
    struct my_emp my_emp;
    int count;
    char buf[BUFFER_SIZE];
    int res;

    printf("query3\n");

    sprintf(buf, "select duty,count(*) from my_emp where duty in ('1','2','3') group by duty order by duty desc");
    printf("SQL: %s\n", buf);
    res=mysqlc_query(buf, -1, &rset);
    if(res != MY_OK)
    {
        printf("错误, sqlcode:%d\n", res);
        return(-1);
    }

    printf("结果：\n");
    for(row_idx=0; row_idx<rset.row_num; row_idx++)
    {
        dbc_col_export_char(&rset, row_idx, 0, my_emp.duty);
        count=dbc_col_export_int(&rset, row_idx, 1);
        printf("%s|%d\n", my_emp.duty, count);
    }
    dbc_query_free(&rset);

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int query4()
{/*{{{*/
    struct dbc_rset rset;
    struct my_emp my_emp;
    struct my_salary my_salary;
    int row_idx;
    char buf[BUFFER_SIZE];
    int res;

    printf("query4\n");

    sprintf(buf, "select a.empno,a.name,b.salary from my_emp a, my_salary b where a.empno=b.empno order by a.empno asc");
    printf("SQL: %s\n", buf);
    res=mysqlc_query(buf, -1, &rset);
    if(res != MY_OK)
    {
        printf("query4: dbc_query fail, sqlcode:%d\n",
            res);
        return(-1);
    }
    printf("结果：\n");
    for(row_idx=0; row_idx<rset.row_num; row_idx++)
    {
        my_emp.empno=dbc_col_export_int64(&rset, row_idx, 0);
        dbc_col_export_char(&rset, row_idx, 1, my_emp.name);
        my_salary.salary=dbc_col_export_double(&rset, row_idx, 2);
        printf("%ld|%s|%.2lf\n", my_emp.empno, my_emp.name,
            my_salary.salary);
    }
    dbc_query_free(&rset);

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int sequence()
{/*{{{*/
    char buf[BUFFER_SIZE];
    int res;
    long nextval;

    printf("sequence\n");

    sprintf(buf, "seq_my_emp");
    printf("SQL: nextval of %s\n", buf);
    res=mysqlc_sequence_nextval(buf, &nextval);
    if(res != MY_OK)
    {
        printf("sequence: dbc_sequence_nextval fail, sqlcode:%d\n",
            res);
        return(-1);
    }
    printf("结果：%ld\n", nextval);

    printf("成功\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

void func()
{/*{{{*/
    int res;

    mysqlc_init();

    res=mysqlc_connect("dbuser", "dbuser", "test", NULL, NULL);
    if(res != MY_OK)
    {
        printf("func: connect fail, sqlcode:%d\n",
            res); 
        return;
    }

    mysqlc_begin();

    if(
            insert()
            || delete()
            || update()
            || query1()
            || query2()
            || query3()
            || query4()
      )
    {
        mysqlc_rollback();
    }
    else
    {
        mysqlc_commit();
    }

    mysqlc_disconnect();

    return;
}/*}}}*/

int main(int argc, char *argv[])
{/*{{{*/
    func();

    exit(0);
}/*}}}*/
